package com.bookmall.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

import com.bookmall.dao.IndexTypeDao;
import com.bookmall.entity.IndexType;
import com.bookmall.util.DBUtil;

public class IndexTypeDaoImpl implements IndexTypeDao {

	@Override
	public boolean insert(IndexType t) throws SQLException {
		// 1.得到连接
		Connection c = DBUtil.getConnection();
		// 2.创建Sql文
		String sql = "insert into index_type_tb(indextypeid,typename,`rank`) values(?,?,?)";
		// 3.得到句柄
		PreparedStatement ps = c.prepareStatement(sql);
		// 4.设置参数
		ps.setInt(1, t.getIndexTypeId());
		ps.setString(2, t.getTypeName());
		ps.setInt(3, t.getRank());
		// 5.执行
		int row = ps.executeUpdate();
		// 6.关闭
		DBUtil.closeConnection(c, ps, null);
		return row == 1 ? true : false;
	}

	@Override
	public boolean update(IndexType t) throws SQLException {
		// TODO 王东泽
		Connection c = DBUtil.getConnection();
		String sql = "update index_type_tb set typename = ?,modify_time = now() where indextypeid = ?";
		PreparedStatement ps = c.prepareStatement(sql);
		ps.setString(1, t.getTypeName());
		ps.setInt(2, t.getIndexTypeId());
		int row = ps.executeUpdate();
		ps.close();
		return row == 1 ? true : false;
	}

	@Override
	public boolean delete(int id) throws SQLException {
		// TODO dz
		Connection c = DBUtil.getConnection();
		String sql = "update index_type_tb set delete_flag = true where indextypeid = ? and delete_flag = false";
		PreparedStatement ps = c.prepareStatement(sql);
		ps.setInt(1, id);
		int row = ps.executeUpdate();
		DBUtil.closeConnection(c, ps, null);
		return row == 1 ? true : false;
	}

	@Override
	public IndexType selectById(int id) throws SQLException {
		Connection c = DBUtil.getConnection();
		String sql = "select indexTypeId,typeName,`rank`,create_time,modify_time from index_type_tb where indexTypeId = ? and delete_flag = 0";
		PreparedStatement ps = c.prepareStatement(sql);
		ps.setInt(1, id);
		ResultSet rs = ps.executeQuery();
		if (rs.next()) {
			IndexType itp = new IndexType();
			itp.setIndexTypeId(rs.getInt(1));
			itp.setTypeName(rs.getString(2));
			itp.setRank(rs.getInt(3));
			itp.setCreateTime(rs.getTimestamp(4).toLocalDateTime());
			if (rs.getTimestamp(5) != null) {
				itp.setModifyTime(rs.getTimestamp(5).toLocalDateTime());
			}
			DBUtil.closeConnection(c, ps, rs);
			return itp;
		} else {
			DBUtil.closeConnection(c, ps, rs);
			return null;
		}

	}

	@Override
	public List<IndexType> selectByPage(int page, int pageSize) throws SQLException {
		// TODO dz
		Connection c = DBUtil.getConnection();
		List<IndexType> indexTypes = new ArrayList<>();
		String sql = "select indexTypeId,typeName,`rank`,create_time,modify_time from index_type_tb where delete_flag = 0 order by `rank` desc limit ?,?";
		PreparedStatement ps = c.prepareStatement(sql);
		ps.setInt(1, (page - 1) * pageSize);
		ps.setInt(2, pageSize);
		ResultSet rs = ps.executeQuery();
		while (rs.next()) {
			IndexType itp = new IndexType();
			itp.setIndexTypeId(rs.getInt(1));
			itp.setTypeName(rs.getString(2));
			itp.setRank(rs.getInt(3));
			itp.setCreateTime(rs.getTimestamp(4).toLocalDateTime());
			if (rs.getTimestamp(5) != null) {
				itp.setModifyTime(rs.getTimestamp(5).toLocalDateTime());
			}
			indexTypes.add(itp);
		}
		DBUtil.closeConnection(c, ps, rs);
		return indexTypes.isEmpty() ? Collections.emptyList() : indexTypes;
	}

}
